package com.wonder.excel;

import com.alibaba.fastjson.JSON;
import com.opencsv.CSVReader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 这个类负责存放excel数据
 *
 */
/**
 *
 * @description: TODO
 * @author jinrong.wang
 * @date 2023.08.13
 * @version 1.0
 */
public class ExcelDataUtils {

    private final static Logger logger = LoggerFactory.getLogger(ExcelDataUtils.class);
    //表头
    public List<String> excel1Head = new ArrayList<>();
    public List<String> excel2Head = new ArrayList<>();
    //excel1和excel2表头集合
    public List<String> excel1Add2Head = new ArrayList<>();
    //head2移除head1的差集
    Set<String> head2SetIntersection = new HashSet<>();
    //head1移除head2的差集
    Set<String> head1SetIntersection = new HashSet<>();


    //excel解析的原始数据
    public List<Map<String, String>> excelFileData_1 = new ArrayList<>();
    public List<Map<String, String>> excelFileData_2 = new ArrayList<>();
    //加工后，带key的原始数据
    public Map<String, Map<String, String>> excelOnlyKey_FileData_1 = new ConcurrentHashMap<String, Map<String, String>>();
    //加工后，带key的原始数据
    public Map<String, Map<String, String>> excelOnlyKey_FileData_2 = new ConcurrentHashMap<String, Map<String, String>>();
    //excel1去除excel2部分
    Set<String> excelIntersection_1 = new HashSet<>();
    //excel2去除excel1部分
    Set<String> excelIntersection_2 = new HashSet<>();



    public void setExcel1Head(List<String> excel1Head) {
        this.excel1Head = excel1Head;
    }

    public void setExcel2Head(List<String> excel2Head) {
        this.excel2Head = excel2Head;
    }


    public void setExcelFileData_1(List<Map<String, String>> excelFileData_1) {
        this.excelFileData_1 = excelFileData_1;
    }

    public void setExcelFileData_2(List<Map<String, String>> excelFileData_2) {
        this.excelFileData_2 = excelFileData_2;
    }

    public void setExcelOnlyKey_FileData_1(Map<String, Map<String, String>> excelOnlyKey_FileData_1) {
        this.excelOnlyKey_FileData_1 = excelOnlyKey_FileData_1;
    }

    public void setExcelOnlyKey_FileData_2(Map<String, Map<String, String>> excelOnlyKey_FileData_2) {
        this.excelOnlyKey_FileData_2 = excelOnlyKey_FileData_2;
    }

    public void initData(File file1, File file2, List<String> nameParams) throws ExcelException, Exception {
        logger.info("初始化数据，开始");
        List<Map<String, String>> excel1Data = this.readExcelToListMap(file1);//将excel文件数据读取到List->Map
        logger.info("初始化数据，file1Num={}",excel1Data.size());
        List<Map<String, String>> excel2Data = this.readExcelToListMap(file2);//将excel文件数据读取到List->Map
        logger.info("初始化数据，file2Num={}",excel2Data.size());
        List<String> excel1Head = this.readExcelHead(file1);//将excel表头读取到List
        List<String> excel2Head = this.readExcelHead(file2);//将excel表头读取到List
        //缓存表头
        this.setExcelFileData_1(excel1Data);//设置属性
        this.setExcelFileData_2(excel2Data);//设置属性
        this.setExcel1Head(excel1Head);//设置属性
        this.setExcel2Head(excel2Head);//设置属性
        this.setExcelOnlyKey_FileData_1(this.getExcelKeyMap(excel1Data, nameParams, excel1Head));
        this.setExcelOnlyKey_FileData_2(this.getExcelKeyMap(excel2Data, nameParams, excel2Head));
        this.megerDate();//合并数据处理
        logger.info("初始化数据，结束");
    }

    /**
     *
     * 返回key->excelRowData
     * @return
     * @throws ExcelException
     */
    public Map<String, Map<String, String>> getExcelKeyMap(List<Map<String, String>> excelData, List<String> nameParams,List<String> excelHead) throws ExcelException{
        //判断nameParams，是否在表头开始
        String checkResult = checkIsInHead(excelHead, nameParams);
        if (checkResult != null) {
            throw new ExcelException("文件1，组合key=" + JSON.toJSONString(nameParams) + "," +
                    "在head里不存在,或者组合主键未输入值；head信息为=" + JSON.toJSONString(excelData));
        }
        logger.info("nameParams=" + JSON.toJSONString(nameParams));
        //判断nameParams，是否在表头结束

        Map<String, Map<String, String>> keyRowData = new HashMap<>();

        for (int i = 0; i < excelData.size(); i++) {
            Map<String, String> excelRowData = excelData.get(i);
            StringBuffer keyStr = new StringBuffer();
            //获取每一条数据的唯一key，如果数据不唯一，则重复数据使用序列号作为key,开始
            for (int k = 0; k < nameParams.size(); k++) {
                String keyName = nameParams.get(k);
                String keyValue = excelRowData.get(keyName);//根据key，获取值
                keyStr.append("_").append(keyValue);
            }
//            logger.info("keyValue={}", keyStr.toString());
            //判断前端输入的表头信息，组合起来的值，是否是excel的唯一主键
            if (keyRowData.get(keyStr.toString()) != null) {
                logger.info("文件1的表头的组合key,在所有数据中不唯一，请检查key={};第{}行与第{}行的组合key结果一样,不一样的值={}"
                        , JSON.toJSONString(nameParams)
                        , i,
                        keyRowData.get(keyStr.toString()).get(Constant.FILEROWID), keyStr.toString()
                );
                //不是唯一主键，重新定义主键fileRowId=rowid，使其唯一
                keyStr = new StringBuffer();
                keyStr.append(excelRowData.get(Constant.FILEROWID));
            }
            //获取每一条数据的唯一key，如果数据不唯一，则重复数据使用序列号作为key,结束
            keyRowData.put(keyStr.toString(), excelRowData);
        }
        return keyRowData;
    }

    /**
     * 将excel每一条数据，按照序列号->唯一key存放
     * 这样方便根据序列找唯一key，再根据唯一key,找数据
     * @param map
     * @return
     */
    public Map<String, String> getDuplicationRowidKeyMap(Map<String, Map<String, String>> map) {
        Map<String, String> duplicationRowidKeyMap = new HashMap<>();
        map.forEach((k,v)->{
            String onlyKey=k;
            String rowid = v.get(Constant.FILEROWID);
            duplicationRowidKeyMap.put(rowid, onlyKey);
        });
        return duplicationRowidKeyMap;
    }

    //获取所有key
    public Set<String> getKeySet(Map<String, Map<String, String>> map) {
        Set<String> keySet = new HashSet<>();
        map.forEach((k,v)->{
            keySet.add(k);
        });
        return keySet;
    }


    //如果返回null表示正常，
    //如果返回值，则返回值在head不不存在
    public String checkIsInHead(List<String> head, List<String> params) {
        for (int i = 0; i < params.size(); i++) {
            String paramName = params.get(i);
            boolean paramsIsInHead=false;//默认不存在

            //检查参数paramName在head是否存在
            for (int k = 0; k < head.size(); k++) {
                String headName = head.get(k);
                //如果paramName在head存在。则比较下一个params
                if (headName.equals(paramName)) {
                    paramsIsInHead=true;
                    break;
                }
            }
            //参数i，在head不存在
            if (!paramsIsInHead) {
                return paramName;
            }
        }
        return null;
    }
    /**
     * 解析excdel
     *
     * @param file1
     * @return
     */
    public List<Map<String, String>> readExcelToListMap(File file1) throws Exception {
        //excel数据放到集合，没一行数据为Map<headName, value>
        List<Map<String, String>> excelData = new ArrayList<Map<String, String>>();

        try (InputStream inputStream = new FileInputStream(file1)) {
            Workbook workbook = null;
            if ((file1.getName().contains("csv"))) {
                //csv转workbook
                workbook = convertCsvToXlsx(inputStream);
            } else {
//                workbook = WorkbookFactory.create(inputStream);
                workbook= new XSSFWorkbook(inputStream);
            }

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            //记录当前excel文件的表头坐标和表头值
            Map<Integer, String> excelHead = new HashMap<Integer, String>();
            //记录行数
            int rowNum = 0;

            // 遍历工作表中的每一行和每一列
            for (Row row : sheet) {
                //读取的每一行数据，存放Map
                Map<String, String> rowData = new HashMap<String, String>();
                rowData.put(Constant.FILEROWID, String.valueOf(rowNum));//自定义id
                //记录列数
                if (rowNum == 0) {
                    for (Cell cell : row) {
                        int index = cell.getColumnIndex();
                        String cellValue = cell.toString();//单元格数据
                        excelHead.put(index, cellValue);
                        rowData.put(cellValue, cellValue);
                    }
                } else {
                    for (Cell cell : row) {
                        int index = cell.getColumnIndex();
                        String cellValue = cell.toString();//单元格数据
                        String headName = excelHead.get(index);
                        rowData.put(headName, cellValue);
                    }
                }
                excelData.add(rowData);
                rowNum++;
            }
        } catch (Exception e) {
            logger.error("readExcelToListMap时异常",e);
            throw e;
        }
        return excelData;
    }

    /**
     * csv转xlsx
     * @param inputStream
     */
    public  Workbook convertCsvToXlsx(InputStream inputStream) {
        logger.info("csv转xslx开始");
        Workbook workbook = new XSSFWorkbook();
        try (CSVReader reader = new CSVReader(new InputStreamReader(inputStream, "GBK"));) {
            Sheet sheet = workbook.createSheet("Sheet1");
            String[] headerRecord = reader.readNext(); // 读取标题行（如果有）
            int rowNum = 0;
            if (headerRecord != null) { // 如果有标题行，写入到XLSX文件的第一行中
                Row headerRow = sheet.createRow(rowNum++);
                for (int i = 0; i < headerRecord.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headerRecord[i]);
                }
            }
            String[] dataRecord;
            while ((dataRecord = reader.readNext()) != null) { // 读取每一行数据并写入到XLSX文件中
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < dataRecord.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(dataRecord[i]);
                }
            }
        } catch (Exception e) {
           logger.error("csv转xlsx失败",e);
        }
        logger.info("csv转xslx完成");
        return workbook;
    }

    /**
     * 读取表头
     * @param file1
     * @return
     */
    public List<String> readExcelHead(File file1) throws Exception {
        List<String> headName = new ArrayList<String>();
        try (InputStream inputStream = new FileInputStream(file1);) {
            Workbook workbook = null;
            if ((file1.getName().contains("csv"))) {
                //csv转workbook
                workbook = convertCsvToXlsx(inputStream);
            } else {
//                workbook = WorkbookFactory.create(inputStream);
                workbook = new XSSFWorkbook(inputStream);

            }
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            //读取第一行
            Row row = sheet.getRow(0);
            if (row == null) {
                throw new ExcelException("文件1，表头为空，请在第一行添加表头");
            }
            //遍历第一行，每一列的值
            for (Cell cell : row) {
                String cellValue = cell.toString();//单元格数据
                //第一行是表头，存储excelHead_1，也当成数据存储在rowData
                headName.add(cellValue);
            }
        } catch (Exception e) {
            logger.error("readExcelHead时异常",e);
            throw e;
        }
        return headName;
    }
    /**
     * 数据合并
     */
    public void megerDate() {
        Set<String> excel1KeySet = this.getKeySet(excelOnlyKey_FileData_1);
        Set<String> excel2KeySet = this.getKeySet(excelOnlyKey_FileData_2);
        //excel1去除excel2部分
        this.excelIntersection_1.addAll(excel1KeySet);
        this.excelIntersection_1.removeAll(excel2KeySet);
        //excel2去除excel1部分
        this.excelIntersection_2.addAll(excel2KeySet);
        this.excelIntersection_2.removeAll(excel1KeySet);

        //1和2的表头各放两份到set
        Set<String> head1Set = new HashSet<>();
        excel1Head.forEach(s -> {
            head1Set.add(s);
            head1SetIntersection.add(s);
            excel1Add2Head.add(s);//存下为了补head2交集
        });
        Set<String> head2Set = new HashSet<>();
        excel2Head.forEach(s -> {
            head2Set.add(s);
            head2SetIntersection.add(s);
        });
        //开始取交集
        head2SetIntersection.removeAll(head1Set);
        head1SetIntersection.removeAll(head2Set);
        //将head2的交集存进全部head
        head2SetIntersection.forEach(s -> {
            excel1Add2Head.add(s);
        });
    }




}
